package com.google.code.netbuilder.dal.providersimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.Date;

import com.google.code.netbuilder.dal.entities.CableWire;
import com.google.code.netbuilder.dal.entities.EquipmentPort;
import com.google.code.netbuilder.dal.providers.IEquipmentPortProvider;
import com.google.code.netbuilder.dal.services.JDBCServise;

public class EquipmentPortProviderJDBC implements IEquipmentPortProvider {

	@Override
	public void save(EquipmentPort entity) {
		String query = "INSERT INTO Equipment_port(id_equipment, id_port, date)" +
				" VALUES (?,?,?);";
		Connection con = null;
		PreparedStatement pst = null;
		try
		{
			con = JDBCServise.getConnection();
			if (con == null) throw new SQLException("No Data Base Connection");
			pst = con.prepareStatement(query);
			pst.setInt(1, entity.getEquipmentId());
			pst.setInt(2, entity.getPortId());
			pst.setDate(3, new Date(entity.getDate().getTime()));
			pst.executeUpdate();
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
				}
		}
	}

	@Override
	public void update(EquipmentPort entity) {
		String query = "UPDATE equipment_port SET id_equipment=?, date=? WHERE id_port=?;";
		
		Connection con = null;
		PreparedStatement pst = null;
		try {
			con = JDBCServise.getConnection();
			if (con == null) throw new SQLException("No Data Base Connection");
			pst = con.prepareStatement(query);
			pst.setInt(1, entity.getEquipmentId());
			pst.setDate(2, new Date(entity.getDate().getTime()));
			pst.setInt(3, entity.getPortId());
			pst.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
				}
		}

	}

	@Override
	public void delete(EquipmentPort entity) {
		String query = "DELETE FROM equipment_port WHERE id_equipment=? AND id_port=?;";
		
		Connection con = null;
		PreparedStatement pst = null;
		try {
			con = JDBCServise.getConnection();
			if (con == null) throw new SQLException("No Data Base Connection");
			pst = con.prepareStatement(query);
			pst.setInt(1, entity.getEquipmentId());
			pst.setInt(2, entity.getPortId());
			pst.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}		
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
				}
		}
	}

	@Override
	public List findAll() {
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<EquipmentPort> equipmentPort = null;
		
		try {
			con = JDBCServise.getConnection();
        
			pst = con.prepareStatement("Select * From Equipment_port;");
			rs = pst.executeQuery();
        
			equipmentPort = new ArrayList<EquipmentPort>();
        
			while (rs.next()) {
				EquipmentPort ep = new EquipmentPort();
				ep.setEquipmentId(rs.getInt("id_equipment"));
				ep.setPortId(rs.getInt("id_port"));
				equipmentPort.add(ep);
			}
		} catch (SQLException exc)
		{
			throw new RuntimeException(exc);
		}
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
				if (rs != null) rs.close();
			} catch (SQLException exc) {
				throw new RuntimeException(exc);
				}
		}	
		
        return equipmentPort;
	}

	@Override
	public EquipmentPort findByID(Integer id) {
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		EquipmentPort ep = null;
		
		try {
			con = JDBCServise.getConnection();
        
			pst = con.prepareStatement("Select * From equipment_port where id_port="+id.toString());
			rs = pst.executeQuery();
        
			if(!rs.first()) throw new SQLException("EquipmetnPort not found");;
			
			
			ep = new EquipmentPort();
			ep.setEquipmentId(rs.getInt("id_equipment"));
			ep.setPortId(rs.getInt("id_port"));			
			
		} catch (SQLException exc)
		{
			throw new RuntimeException(exc);
		}
		finally
		{
			try {
				if (con != null) con.close();
				if (pst != null) pst.close();
				if (rs != null) rs.close();
			} catch (SQLException exc) {
				throw new RuntimeException(exc);
				}
		}
		
        return ep;
	}

}
